import pymysql


class MySqlTool(object):
    def __init__(self):
        self.conn = pymysql.connect(host='114.67.234.65', port=3306, user='aivin', passwd='Hnyer88888888',
                                    db='aivininfo', charset='UTF8')
        print("获得数据库连接:")
        print(self.conn)
        self.cursor = self.conn.cursor()

    def addAuthor(self ,author):
        sql = " insert  into poetryauthor( mname, msex , mdesc, mpic,mtime,mtype ) values(  '%s','%s','%s','%s','%s','%s'  ) " \
              % (author.mname, author.msex, author.mdesc, author.mpic, author.mtime, author.mtype)
        self.cursor.execute(sql)
        newId = int(self.conn.insert_id())
        self.conn.commit()
        print("插入 新记录的id是:  ", newId)

    def deleteAuthorById(self, mId):
        sql = 'delete from poetryauthor where mId = %d  ' % (mId)
        count = self.cursor.execute(sql)
        self.conn.commit()
        print("删除结果：", count)

    def updateAutorById(self, author, mId):
        sql = " update poetryauthor set mname= '%s', msex='%s', mdesc='%s', mpic='%s', mtime='%s' , mtype='%s' where mId=%d " \
              % (author.mname, author.msex, author.mdesc, author.mpic, author.mtime, author.mtype, mId)
        count = self.cursor.execute(sql)
        print("更新结果：", count)
        self.conn.commit()

    def queryAuthor(self):
        count = self.cursor.execute('select * from poetryauthor')
        print('共有' + str(count) + ' 条记录')
        rows = self.cursor.fetchall()
        for row in rows:
            mid = row[0]
            mname = row[1]
            msex = row[2]
            mdesc = row[3]
            mpic = row[4]
            mtime = row[5]
            mtype = row[6]
            print(mid, mname, msex, mdesc, mpic, mtime, mtype)

    def closeConnection(self):
        # 关闭连接等...
        self.cursor.close()
        self.conn.commit()
        self.conn.close()

# author = Author("李白2", "男1", "牛逼诗人1", "baidu.com...png1", "唐代1", "浪漫主义1")
# sqlTool = MySqlTool()
# sqlTool.addAuthor(author)
# sqlTool.deleteAuthorById(5)
# sqlTool.updateAutorById(author , 6)
# sqlTool.sqlTool.queryAuthor()
# sqlTool.closeConnection()